package com.qf.admin.dao.impl;

import com.qf.admin.dao.AddressDao;
import com.qf.admin.domain.AddAddress;
import com.qf.admin.domain.Address;
import com.qf.admin.domain.Orders;
import com.qf.commons.TableData;
import com.qf.utils.DataUtils;
import com.qf.utils.DbUtils;
import com.qf.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class AddressDaoImpl implements AddressDao {

    QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

    @Override
    public List<Address> getAddressByUserId(Integer userId) {
        List<Address> list = null;
        String sql = "select a_id id, a_name name, a_phone phone, a_detail detail, a_state status, a_take_delivery_state takeDeliveryStatus from address where u_id = ?";

        Connection conn = DbUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, userId);
            ResultSet rs = ps.executeQuery();

            list = DataUtils.getAll(Address.class, rs);

            DbUtils.colse(rs, ps, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public void changeTakeDeliveryAddress(Integer userId, Integer addressId) {
        // 将以前的收货地址重置。
        String updateSql = "update address set a_take_delivery_state = 0 where a_take_delivery_state = 1 and u_id = ?";

        //将给定的值该设置为收货地址
        String updateSql2 = "update address set a_take_delivery_state = 1 where a_id = ?";

        Connection conn = DbUtils.getConnection();
        try {
            conn.setAutoCommit(false);

            PreparedStatement ps = conn.prepareStatement(updateSql);
            ps.setObject(1, userId);
            ps.executeUpdate();

            ps = conn.prepareStatement(updateSql2);
            ps.setObject(1, addressId);
            ps.executeUpdate();

            DbUtils.colse(null, ps, null);

            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public void addAddress(Integer userId, String addressName, String phone, String detail,Integer state, Connection connection) {
        String sql = "insert into address(u_id,a_name,a_phone,a_detail,a_state) values(?,?,?,?,?)";
        try {
            queryRunner.update(connection,sql, userId, addressName, phone, detail, state);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void updateAddress(Integer userId, String addressName, String phone, String detail, Integer addressId, Connection connection) {
        String sql = "update address set a_name=?,a_phone=?,a_detail=? where u_id=? and a_id=?";
        try {
            queryRunner.update(connection,sql, addressName, phone, detail, userId, addressId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void changeState(Integer userId, Connection connection) {
        //将默认地址设置为非默认地址
        String sqlState = "update address set a_state=0 where a_state=1 and u_id=?";
        try {
            queryRunner.update(connection,sqlState, userId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void deleteAddress(Integer userId, Integer addressId) {
        String sql = "delete from address where u_id=? and a_id=?";
        try {
            queryRunner.update(sql, userId, addressId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Address getAddressByAid(Integer aid) {
        String sql = "select a_id id, a_name name, a_phone phone, a_detail detail, a_state status, a_take_delivery_state takeDeliveryStatus from address where a_id = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(Address.class), aid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    ////获取订单管理的信息
    @Override
    public TableData<Orders> getAddress(String name,String goods,Integer status,Integer number,Date beginDate,Date endDate,Integer limit, Integer offset) {

        TableData<Orders> tableData = new TableData<Orders>();

        StringBuffer getAddressSql = new StringBuffer("SELECT a.a_id a_id,a.u_id u_id, c.o_id id,c.o_no order_no,c.o_good_title goods,c.o_num number,c.o_good_price price,c.o_status status,c.o_create_time create_time,a.a_name username, a.a_phone usernamePhone,a.a_detail userAddress from address a INNER JOIN order_address b ON a.a_id = b.o_address");
        getAddressSql.append(" INNER JOIN orders c ON b.o_no = c.o_no where 1 = 1 ");

        System.out.println(getAddressSql.toString());

//        String getAddressSql = "SELECT a.a_id a_id,a.u_id u_id, c.o_id id,c.o_no order_no,c.o_good_title goods,c.o_num number,c.o_good_price price,c.o_status status,c.o_create_time create_time,a.a_name username, a.a_phone usernamePhone,a.a_detail userAddress from address a INNER JOIN order_address b ON a.a_id = b.o_address" +
//                " INNER JOIN orders c ON b.o_no = c.o_no order by c.o_create_time desc limit ?, ? ";
//        String AddressTotalSql ="select count(1) from address a INNER JOIN order_address b ON a.a_id = b.o_address INNER JOIN orders c ON b.o_no = c.o_no ";

          List<Object> paramsList = new ArrayList<>();

        // 如果不输入订单号， 就不用加条件查询
        if(null != name && !"".equals(name)) {  // "    "
            getAddressSql.append(" and c.o_no = ? ");
            paramsList.add(name);
        }

        if(null != goods && !"".equals(goods)) {  // "    "
            getAddressSql.append(" and c.o_good_title = ? ");
            paramsList.add(goods);
        }

        if(null != status && -1 != status) {
            getAddressSql.append(" and c.o_status = ? ");
            paramsList.add(status);
        }

        if(null != number && 0 !=number) {
            getAddressSql.append(" and c.o_num = ? ");
            paramsList.add(number);
        }

        if(null != beginDate) {
            getAddressSql.append(" and c.o_create_time >= ? ");
            paramsList.add(beginDate);
        }

        if(null != endDate) {
            getAddressSql.append(" and c.o_create_time <= ? ");
            paramsList.add(endDate);
        }

        getAddressSql.append("order by c.o_create_time desc");
        getAddressSql.append(" limit ?, ? ");
        paramsList.add(offset);
        paramsList.add(limit);



        
        try {
            List<Orders> address = queryRunner.query(getAddressSql.toString(), new BeanListHandler<Orders>(Orders.class),paramsList.toArray());

            Long total1 = getTotal(name, goods, status, number,beginDate, endDate);

            Integer total = total1.intValue();


            tableData.setRows(address);
            tableData.setTotal(total);
            return tableData;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    private long getTotal(String name,String goods,Integer status,Integer number,Date beginDate,Date endDate){
        StringBuffer getTotalSql = new StringBuffer("select count(1) from address a INNER JOIN order_address b ON a.a_id = b.o_address INNER JOIN orders c ON b.o_no = c.o_no where 1 =1");
        List<Object> paramsList1 = new ArrayList<>();

        if(null != name && !"".equals(name)) {  // "    "
            getTotalSql.append(" and c.o_no = ? ");
            paramsList1.add(name);
        }

        if(null != goods && !"".equals(goods)) {  // "    "
            getTotalSql.append(" and c.o_good_title = ? ");
            paramsList1.add(goods);
        }

        if(null != status && -1 != status) {
            getTotalSql.append(" and c.o_status = ? ");
            paramsList1.add(status);
        }

        if(null != number && 0 != number) {
            getTotalSql.append(" and c.o_num = ? ");
            paramsList1.add(number);
        }

        if(null != beginDate) {
            getTotalSql.append(" and c.o_create_time >= ? ");
            paramsList1.add(beginDate);
        }

        if(null != endDate) {
            getTotalSql.append(" and c.o_create_time <= ? ");
            paramsList1.add(endDate);
        }

        try {
            Long total = queryRunner.query(getTotalSql.toString(), new ScalarHandler<>(), paramsList1.toArray());
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;

    }



    //改变订单的默认收货地址
    @Override
    public void changeOrderAddress(Integer userId,Integer aid,Integer changeId) {

        // 将以前的收货地址重置。
        String updateSql = "update address set a_take_delivery_state = 0 where a_take_delivery_state = 1 and u_id = ?";

        //将给定的值该设置为收货地址
        String updateSql2 = "update address set a_take_delivery_state = 1 where a_id = ?";

        //换订单地址用户id
        String updateSql3 ="UPDATE address a INNER JOIN order_address b ON a.a_id = b.o_address SET b.o_address = ? where  a.a_id = ?";



        try {
            queryRunner.update(updateSql,userId);
            queryRunner.update(updateSql2,aid);
            queryRunner.update(updateSql3,changeId,aid);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    ////获取订单对应用户的信息
    @Override
    public Orders getAddressUser(Integer id) {

        String getAddressUserSql = "SELECT a.a_id a_id,a.u_id u_id,c.o_id id,c.o_no order_no,c.o_good_title goods,c.o_num number,c.o_good_price price,c.o_status status,c.o_create_time create_time,a.a_name username, a.a_phone usernamePhone,a.a_detail userAddress from address a INNER JOIN order_address b ON a.a_id = b.o_address " +
                "INNER JOIN orders c ON b.o_no = c.o_no  WHERE c.o_id =?";

        try {
            Orders addressUser = queryRunner.query(getAddressUserSql, new BeanHandler<Orders>(Orders.class),id);
            return addressUser;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return null;
    }


    //获取可选地址
    @Override
    public  List <AddAddress> getAddAddress(Integer userId) {



        String getAllAddressSql ="select a_detail address,a_id id from address where u_id = ?" ;



        try {
            List<AddAddress> allAddress = queryRunner.query(getAllAddressSql, new BeanListHandler<AddAddress>(AddAddress.class),userId);
            return allAddress;

        } catch (SQLException throwables) {
        }
      return  null;
    }

    @Override
    public void changeDefaultAddress(int userId, int addressId) {
        // 将以前的默认地址重置。
        String updateSql = "update address set a_state = 0 where a_state = 1 and u_id = ?";
        //将给定的值该设置为默认地址
        String updateSql2 = "update address set a_state = 1 where a_id = ?";

        try {
            queryRunner.update(updateSql,userId);
            queryRunner.update(updateSql2,addressId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


}
